﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_LogFileSize]
#-- Purpose:		Maintains the size of the log file and the vlf increments
#--					in the log file to reduce fragmentation and speed up 
#--					performance and database startup.
#--	Last Update:	07/14/2016
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#-- Readings:		http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
#--					http://sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
#--					http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
#--					http://sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
#--					http://www.karaszi.com/SQLServer/info_dont_shrink.asp
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_LogFileSize]
(
	@log_pct_total			int		= 10, 
	@log_pct_used			int		= 25,
	@min_disk_free_pct		int		= 15,
	@debug_only				bit		= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@database_name sysname, @sSQL varchar(MAX), @vlf_optimal int, @vlf_minimal int,
			@pct_log_total float, @pct_log_used float, @major int, @TemplateLogGrowth nvarchar(MAX)
DECLARE		@diskspace TABLE (dbd_drive nvarchar(256), dbd_capacityMB varchar(256), dbd_usedMB varchar(256), dbd_freeMB varchar(256), dbd_free_pct varchar(6))
DECLARE		@logsize TABLE (database_id int, file_id int, name sysname, physical_name nvarchar(260), logfile_size_MB bigint, logfile_count int)
DECLARE		@file_size TABLE (database_id int, Fileid int, FileGroup int, TotalExtents int, UsedExtents int, name sysname, physical_name nchar(520))
DECLARE		@log_info TABLE (database_id int, RecoveryUnitId int, FileID int, FileSize numeric(20,0), StartOffset bigint, FSeqNo bigint, Status tinyint, Parity tinyint, CreateLSN numeric(25,0))
DECLARE		@progname sysname
DECLARE	@results TABLE
(
	log_action				varchar(30),
	act_drive				char(2),
	database_id				int,
	name					sysname,
	physical_name			nvarchar(260),
	total_MB				bigint,
	used_MB					bigint,
	logfile_size_MB			bigint,
	logfile_count			int,
	current_vlf_count		int,
	minimum_vlf_count		int,
	minimum_vlf_sizeMB		bigint,
	suggested_logsize_MB	bigint,
	suggested_vlfsize_MB	int,
	suggested_vlf_count		int,
	log_incremental_size_MB	int,
	log_incremental_count	int,
	recovery_model			sysname
)
DECLARE @tAOAGStat TABLE ([database_id] int, [database_name] nvarchar(256))

--- Define the name of this program (for logs)
SELECT		@progname = 'spMaint_LogFileSize',
			@TemplateLogGrowth = 'ALTER DATABASE [VAR_DBNAME] MODIFY FILE ( NAME = N''[VAR_FILENAME]'', FILEGROWTH = [VAR_SIZEMB]MB )'

--- Initialize Local Variables
SELECT	@pct_log_total = @log_pct_total / 100.0,
		@pct_log_used = @log_pct_used / 100.0,
		@vlf_optimal = 500, 
		@vlf_minimal = 250,
		@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int)

--- Determine if this is an AlwaysOn setup - and if so which databases are in AlwaysOn and this server is the primary.
IF @major > 10
  BEGIN
	IF OBJECT_ID('[sys].[dm_hadr_availability_group_states]') IS NOT NULL
		SELECT @sSQL = 'SELECT		t2.[database_id],
			t2.[name] [database_name]
FROM		[sys].[availability_replicas] t1
JOIN		[sys].[databases] t2 ON t1.[replica_id] = t2.[replica_id]
JOIN		[sys].[availability_groups] t3 ON t1.[group_id] = t3.[group_id]
LEFT JOIN	[sys].[dm_hadr_availability_group_states] t4 ON t1.[group_id] = t4.[group_id]
WHERE		t1.[replica_server_name] <> t4.[primary_replica]'
	ELSE
		SELECT	@sSQL = 'SELECT 0, SPACE(0) WHERE 1 = 0'

	INSERT INTO @tAOAGStat EXEC (@sSQL)
  END

--- Get Current Disk Information
INSERT INTO @diskspace (dbd_drive, dbd_capacityMB, dbd_usedMB, dbd_freeMB, dbd_free_pct) EXEC [dbo].[spIO_GetVolumeSpace] ''

--- Get log file sizing for the requested database
INSERT INTO @logsize
	SELECT		mf.database_id,
				mf.file_id,
				mf.name,
				mf.physical_name,
				SUM(mf.size * 8 / 1024),
				COUNT(*)
	FROM		[master].[sys].[databases] db
	JOIN		[master].[sys].[master_files] mf ON db.[database_id] = mf.[database_id]
	LEFT JOIN	@tAOAGStat t2 ON db.[name] = t2.[database_name]
	WHERE		db.name NOT IN ('tempdb', 'ReportServerTempDB', 'model')
				AND db.name NOT IN (SELECT database_name FROM [dbo].[vwMaint_DoNotMaintain])
				AND db.state = 0
				AND db.is_read_only = 0
				AND db.source_database_id IS NULL  -- Cannot change file sizes on Snapshots
				AND DB_NAME(mf.database_id) NOT IN ('tempdb', 'model')
				AND DB_NAME(mf.database_id) NOT IN (SELECT database_name FROM [dbo].[vwMaint_DoNotMaintain])
				AND mf.type = 1
				AND t2.[database_name] IS NULL
	GROUP BY	mf.database_id,
				mf.file_id,
				mf.name,
				mf.physical_name

--- Retrieve the size of the data files and database log file info
DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
	SELECT		db.name
	FROM		[master].[sys].[databases] db
	LEFT JOIN	@tAOAGStat t2 ON db.[name] = t2.[database_name]
	WHERE		db.name NOT IN ('tempdb', 'ReportServerTempDB', 'model')
				AND db.name NOT IN (SELECT database_name FROM [dbo].[vwMaint_DoNotMaintain])
				AND db.state = 0
				AND db.is_read_only = 0
				AND db.source_database_id IS NULL  -- Cannot change file sizes on Snapshots
				AND t2.[database_name] IS NULL
	ORDER BY	db.database_id
OPEN crs
FETCH NEXT FROM crs INTO @database_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Retrieve the size of the data files
	SELECT	@sSQL = 'USE ' + QUOTENAME(@database_name) + '; DBCC SHOWFILESTATS WITH NO_INFOMSGS;'
	INSERT INTO @file_size (Fileid, FileGroup, TotalExtents, UsedExtents, name, physical_name) 
		EXEC (@sSQL)
	UPDATE @file_size SET database_id = DB_ID(@database_name) WHERE database_id IS NULL

	--- Retrieve the database log file info
	SELECT	@sSQL = 'DBCC LOGINFO(''' + @database_name + ''') WITH NO_INFOMSGS;'
	IF @major > 10
		INSERT INTO @log_info (RecoveryUnitId, FileID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN) 
			EXEC (@sSQL)
	ELSE
		INSERT INTO @log_info (FileID, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN) 
			EXEC (@sSQL)
	UPDATE @log_info SET database_id = DB_ID(@database_name) WHERE database_id IS NULL
	
	FETCH NEXT FROM crs INTO @database_name
  END
CLOSE crs
DEALLOCATE crs

--- Determine if the log file is overly fragmented and rebuild if necessary
;WITH data_size AS
(
	SELECT		database_id = database_id,
				total_MB = (CAST(LEFT(CAST([logfile_size_MB] as varchar(1000)), 1) as int) + 1) * POWER(10, LEN([logfile_size_MB]) - 1),
				used_MB = (CAST(LEFT(CAST([logfile_size_MB] as varchar(1000)), 1) as int) + 1) * POWER(10, LEN([logfile_size_MB]) - 1),
				suggested_logsize_MB = 
					(CASE	WHEN [logfile_size_MB] < 1 THEN 1
							WHEN [logfile_size_MB] < 10 THEN [logfile_size_MB]
							WHEN [logfile_size_MB] < 1000 AND [logfile_size_MB] % 10 = 0 THEN [logfile_size_MB]
							WHEN [logfile_size_MB] < 1000 THEN (([logfile_size_MB] / 10) + 1) * 10
							WHEN [logfile_size_MB] % 100 = 0 THEN [logfile_size_MB]
							ELSE (([logfile_size_MB] / 100) + 1) * 100
							END),
				current_vlf_count = (SELECT COUNT(*) FROM @log_info li WHERE li.database_id = t1.database_id),
				minimum_vlf_count = (SELECT COUNT(*) FROM @log_info li WHERE li.database_id = t1.database_id AND CreateLSN = 0),
				minimum_vlf_sizeMB = CAST((SELECT SUM(li.FileSize) / 1024 / 1024 FROM @log_info li WHERE li.database_id = t1.database_id AND CreateLSN = 0) as int)
	FROM		@logsize t1
),
calc_size AS
(
	SELECT		database_id,
				total_MB,
				used_MB,
				suggested_logsize_MB,
				minimum_vlf_sizeMB,
				(suggested_logsize_MB - minimum_vlf_sizeMB) growthMB,
				(CASE	WHEN suggested_logsize_MB < 64 THEN (64/4)
						WHEN suggested_logsize_MB < 1024 THEN (1024/8)
						WHEN 125 * (40 - minimum_vlf_count) >= (suggested_logsize_MB - minimum_vlf_sizeMB) THEN 125
						WHEN 250 * (40 - minimum_vlf_count) >= (suggested_logsize_MB - minimum_vlf_sizeMB) THEN 250
						WHEN 500 * (40 - minimum_vlf_count) >= (suggested_logsize_MB - minimum_vlf_sizeMB) THEN 500
						ELSE (suggested_logsize_MB - minimum_vlf_sizeMB) / (40 - minimum_vlf_count)
						END) suggested_vlfsize_MB,
				(CASE	WHEN suggested_logsize_MB < 64 THEN 4
						WHEN suggested_logsize_MB < 1024 THEN 8
						ELSE 16 END) suggested_vlf_count,
				current_vlf_count,
				minimum_vlf_count
	FROM		data_size
),
action_ds AS
(
	SELECT		ls.database_id,
				ls.name,
				ls.physical_name,
				cs.total_MB,
				cs.used_MB,
				ls.logfile_size_MB,
				ls.logfile_count,
				cs.current_vlf_count,
				cs.minimum_vlf_count,
				cs.minimum_vlf_sizeMB,
				cs.suggested_logsize_MB,
				cs.suggested_vlfsize_MB,
				cs.suggested_vlf_count,
				(cs.suggested_vlfsize_MB * cs.suggested_vlf_count) log_incremental_size_MB,
				(CASE	WHEN cs.suggested_logsize_MB % (cs.suggested_vlfsize_MB * cs.suggested_vlf_count) = 0 
						THEN cs.suggested_logsize_MB / (cs.suggested_vlfsize_MB * cs.suggested_vlf_count)
						ELSE (cs.suggested_logsize_MB / (cs.suggested_vlfsize_MB * cs.suggested_vlf_count)) + 1
						END) log_incremental_count,
				(SELECT d.recovery_model_desc FROM [master].[sys].[databases] d WHERE name = DB_NAME(ls.[database_id])) recovery_model
	FROM		calc_size cs
	JOIN		@logsize ls ON cs.database_id = ls.database_id
)
INSERT INTO @results (
		act_drive, database_id, name, physical_name, total_MB, used_MB, logfile_size_MB, 
		logfile_count, current_vlf_count, minimum_vlf_count, minimum_vlf_sizeMB, 
		suggested_logsize_MB, suggested_vlfsize_MB, suggested_vlf_count,
		log_incremental_size_MB, log_incremental_count, recovery_model
	)
	SELECT		SUBSTRING(act.physical_name, 1, 2) act_drive,
				act.*
	FROM		action_ds act

--- Determine which action needs to be taken per database
UPDATE		res
SET			log_action = (CASE	
					WHEN logfile_count > 1 THEN 'Too Many Logfiles (> 1)'
					WHEN current_vlf_count > 40 AND current_vlf_count > ((suggested_vlf_count * log_incremental_count) + minimum_vlf_count)
							THEN 'Rebuild (Too Many VLF)'
					WHEN (res.log_incremental_count - 1) * res.suggested_vlf_count > res.current_vlf_count THEN 'Rebuild (Too Few VLF'
					WHEN suggested_logsize_MB > logfile_size_MB THEN 'Grow'
					WHEN suggested_logsize_MB < logfile_size_MB THEN 'Shrink'
					ELSE 'No Change' END)
FROM		@results res

--- These variables only apply to the cursor below, so I wanted the definition
---		to be down here near the action
DECLARE	@recovery_model sysname, @database_id int, @name sysname, @suggested_logsize_MB bigint,
		@log_incremental_size_MB bigint, @log_incremental_count int, @iCount int, @minimum_logsize_MB bigint,
		@suggested_vlf_count int

--- If in debug, display recordset
IF @debug_only = 1
  BEGIN
	SELECT		DB_NAME(res.database_id) database_name,
				res.*
	FROM		@results res
	ORDER BY	log_action DESC, DB_NAME(database_id)
  END
  
--- Process the changes to the file size
DECLARE crs CURSOR FOR
	SELECT		recovery_model,
				database_id,
				name,
				res.minimum_vlf_sizeMB,
				suggested_logsize_MB,
				log_incremental_size_MB,
				log_incremental_count,
				res.suggested_vlf_count
	FROM		@results res
	WHERE		ISNULL(log_action, 'No Change') <> 'No Change'
	ORDER BY	log_action DESC, DB_NAME(database_id)
OPEN crs
FETCH NEXT FROM crs INTO @recovery_model, @database_id, @name, @minimum_logsize_MB, @suggested_logsize_MB, @log_incremental_size_MB, @log_incremental_count, @suggested_vlf_count
WHILE @@FETCH_STATUS <> -1
  BEGIN
	--- Different course of action based on the recovery_model
	IF @recovery_model = 'SIMPLE'
	  BEGIN
		--- For simple recovery, Checkpoint
		SELECT	@sSQL = 'USE ' + QUOTENAME(DB_NAME(@database_id))  + '; CHECKPOINT;'
		IF @debug_only = 1
			PRINT @sSQL
		ELSE
		  BEGIN
			EXEC (@sSQL)

			--- Log to the SQL Server error log
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
		  END
	  END
	  
	ELSE
	  BEGIN
		--- For non-simple recovery, backup log
		SELECT	@sSQL = 'EXEC [dbo].[spBkp_Log] @database_name = ''' + DB_NAME(@database_id) + ''', @ignore_skip = 1;'
		IF @debug_only = 1
			PRINT @sSQL
		ELSE
		  BEGIN
			EXEC (@sSQL)
			
			--- Log to the SQL Server error log
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
		  END

		--- Shrink the log file to nothing (or as close as you can get).
		---		Doing an extra backup after this shrink and then another shrink
		---		seems to be necessary to get the active VLF back to the beginning
		---		of the transaction log in a non SIMPLE recovery model.
		SELECT	@sSQL = 'EXEC [dbo].[spIO_ShrinkFile] @database_name = ''' + DB_NAME(@database_id) + ''', @minimum_size_MB = ' + CAST(@minimum_logsize_MB as varchar(1000)) + ', @is_type_log_file = 1;'
		IF @debug_only = 1
			PRINT @sSQL
		ELSE
		  BEGIN
			EXEC (@sSQL)
				
			--- Log to the SQL Server error log
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
		  END
		  
		--- For non-simple recovery, backup log
		SELECT	@sSQL = 'EXEC [dbo].[spBkp_Log] @database_name = ''' + DB_NAME(@database_id) + ''', @ignore_skip = 1;'
		IF @debug_only = 1
			PRINT @sSQL
		ELSE
		  BEGIN
			EXEC (@sSQL)
			
			--- Log to the SQL Server error log
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
		  END
	  END

	--- *******************************************************************
	--- The following applies regardless of recovery model
	--- *******************************************************************

	--- Shrink the log file to nothing (or as close as you can get)
	SELECT	@sSQL = 'EXEC [dbo].[spIO_ShrinkFile] @database_name = ''' + DB_NAME(@database_id) + ''', @minimum_size_MB = ' + CAST(@minimum_logsize_MB as varchar(1000)) + ', @is_type_log_file = 1;'
	IF @debug_only = 1
		PRINT @sSQL
	ELSE
	  BEGIN
		EXEC (@sSQL)
			
		--- Log to the SQL Server error log
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
	  END
	
	--- Grow the log file the specified number of times and increments
	IF @suggested_logsize_MB > 1024
	  BEGIN
		--- The new logsize is more than 1GB, so there is a loop to create uniform VLFs <= 500 MB
		SET @iCount = @minimum_logsize_MB
		WHILE @iCount < @suggested_logsize_MB
		  BEGIN
			IF @iCount + @log_incremental_size_MB <= @suggested_logsize_MB
				SET @iCount = @iCount + @log_incremental_size_MB
			ELSE
				SET @iCount = @suggested_logsize_MB

			--- Grow the log file
			SELECT @sSQL = 'ALTER DATABASE ' + QUOTENAME(DB_NAME(@database_id)) + ' MODIFY FILE ( NAME = N''' + @name + ''', SIZE = ' + CAST(@iCount as varchar(1000)) + 'MB )'
			IF @debug_only = 1
				PRINT @sSQL
			ELSE
			  BEGIN
				EXEC (@sSQL)
				
				--- Log to the SQL Server error log
				EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
			  END
		  END
	  END
	ELSE
	  BEGIN
		--- Get file current size
		DECLARE @size_now bigint
		SELECT	@size_now = (size * 8 / 1024)
		FROM	[master].[sys].[master_files]
		WHERE	database_id = @database_id
				AND name = @name
		
		--- If after the shrinks above, the current size is larger than the suggested size - skip
		IF @size_now < @suggested_logsize_MB
		  BEGIN
			--- Grow the log file
			SELECT @sSQL = 'ALTER DATABASE ' + QUOTENAME(DB_NAME(@database_id)) + ' MODIFY FILE ( NAME = N''' + @name + ''', SIZE = ' + CAST(@suggested_logsize_MB as varchar(1000)) + 'MB )'
			IF @debug_only = 1
				PRINT @sSQL
			ELSE
			  BEGIN
				EXEC (@sSQL)
				
				--- Log to the SQL Server error log
				EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
			  END
		  END
	  END

	--- Move to the next database
	FETCH NEXT FROM crs INTO @recovery_model, @database_id, @name, @minimum_logsize_MB, @suggested_logsize_MB, @log_incremental_size_MB, @log_incremental_count, @suggested_vlf_count
  END
CLOSE crs
DEALLOCATE crs

--- Process changes to the increment of growth
DECLARE crsLogGrowth CURSOR FOR
	SELECT		REPLACE(
					REPLACE(
						REPLACE(@TemplateLogGrowth, '[VAR_DBNAME]', QUOTENAME(DB_NAME(mf.[database_id])))
					, '[VAR_FILENAME]', mf.[name])
				, '[VAR_SIZEMB]', CAST(
							(CASE	WHEN (mf.[size]*8/1024) < 10 THEN 1
									WHEN (mf.[size]*8/1024) < 1000 THEN 10
									ELSE 100
									END) as varchar(4000)))
	FROM		[master].[sys].[databases] db
	JOIN		[master].[sys].[master_files] mf ON db.[database_id] = mf.[database_id]
	LEFT JOIN	@tAOAGStat t2 ON db.[name] = t2.[database_name]
	WHERE		db.name NOT IN ('tempdb', 'ReportServerTempDB', 'model')
				AND db.name NOT IN (SELECT database_name FROM [dbo].[vwMaint_DoNotMaintain])
				AND db.state = 0
				AND db.is_read_only = 0
				AND db.source_database_id IS NULL  -- Cannot change file sizes on Snapshots
				AND mf.[type] = 1
				AND (
					mf.[is_percent_growth] = 1
					OR (mf.[growth]*8/1024) <> 
							(CASE	WHEN (mf.[size]*8/1024) < 10 THEN 1
									WHEN (mf.[size]*8/1024) < 1000 THEN 10
									ELSE 100
									END)
				)
				AND t2.[database_name] IS NULL
OPEN crsLogGrowth
FETCH NEXT FROM crsLogGrowth INTO @sSQL
WHILE @@FETCH_STATUS <> -1
  BEGIN
	IF @debug_only = 1
		PRINT	@sSQL
	ELSE
	  BEGIN
		EXEC	(@sSQL)

		--- Log to the SQL Server error log
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sSQL
	  END

	FETCH NEXT FROM crsLogGrowth INTO @sSQL
  END
CLOSE crsLogGrowth
DEALLOCATE crsLogGrowth

SET NOCOUNT OFF
